package reaction.news.index;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.lucene.document.Document;
import org.apache.lucene.document.Field;

import reaction.news.db.MySQLConnection;
import reaction.news.db.Utils;

public class Politician {
	
	//String Wikpedia_URI;
	//String DBPedia_URI;
	String power_URI;
	String name;
	
	int person;
	
	List<NewsArticle> news;
	
	public Politician(int person_, String name_, String power_URI_){
		super();
		this.person = person_;
		this.name = name_;
		this.power_URI = power_URI_;
	}
	
	public Document getNews() throws Exception{
		List<Integer> ids = getNewsIDs();
		getNewsArticles(ids);
		return newsText();
	}
	
	public List<Integer> getNewsIDs() throws Exception {
		
		// Connect to BD
		Connection connect = MySQLConnection.getConnection("publico", "publ1c0", "jdbc:mysql://reactionvm01/news-entities");
	
		//get all news associated to "person"
		String SQLString = "SELECT news_id FROM news WHERE id_power_entities = ?";	
		PreparedStatement pstmt =  connect.prepareStatement(SQLString);
		pstmt.setInt(1, person);
		
		ResultSet news = pstmt.executeQuery();
		
		List<Integer> ids = new ArrayList<Integer>();
		
		while (news.next()) {
			ids.add(news.getInt(1));
		}
		
		return ids;
		
	}
	
	
	public void getNewsArticles(List<Integer> ids) throws Exception, SQLException {
		
		final String SQL_FIND = "SELECT id, title, subtitle, newstext FROM politica WHERE id IN (%s)";
		
		Connection connection = null;
		PreparedStatement pstmt =  null;
		ResultSet resultSet = null;
	    List<NewsArticle> list = new ArrayList<NewsArticle>();
		String sql = String.format(SQL_FIND, Utils.preparePlaceHolders(ids.size()));
		
		try{
			connection = MySQLConnection.getConnection("publico", "publ1c0","jdbc:mysql://agatha/publico-news");
			pstmt = connection.prepareStatement(sql);
	        Utils.setValues(pstmt, ids.toArray());
	        resultSet = pstmt.executeQuery();
	        while (resultSet.next()) {
	        	NewsArticle news = new NewsArticle();
	        	news.id = resultSet.getInt("id");
	        	news.title = resultSet.getString("title");
	        	news.subtitle = resultSet.getString("subtitle");
	        	news.text = resultSet.getString("newstext");
	            list.add(news);
	        }
	    } finally {
	        //close(connection, pstmt, resultSet);
	    	connection.close();
	    	pstmt.close(); 
	    	resultSet.close();
	    }

	    this.news = list;		
	}
	
	
	public Document newsText(){
		
		StringBuffer text = new StringBuffer();
		
		for (NewsArticle article : news) {
			
			text.append(article.title);
			text.append("\n"+article.subtitle);
			text.append("\n"+article.text);
		}
		
		Document doc = new Document();
	
		doc.add(new Field("id", Integer.toString(this.person), Field.Store.YES, Field.Index.NOT_ANALYZED, Field.TermVector.NO));
		doc.add( new Field("power_uri", this.power_URI, Field.Store.YES, Field.Index.NOT_ANALYZED, Field.TermVector.NO));
		doc.add( new Field("name", this.name, Field.Store.YES, Field.Index.NOT_ANALYZED, Field.TermVector.NO));		
		doc.add(new Field("text", text.toString(), Field.Store.NO, Field.Index.ANALYZED, Field.TermVector.YES));
		return doc;
		
	}	
}
